﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LPY.LeHealth.DTO;
namespace LPY.LeHealth.DAL
{
    using ServiceStack.OrmLite;

    public class ComboDAL : GenericBaseDAL<ComboDTO>
    {
       /// <summary>
       ///根据使用人群显示套餐
       /// </summary>
       /// <param name="Appropriate"></param>
       /// <returns></returns>
      public List<ComboDTO> GetCombo()
      {
          var sqlExpression = DbConnection.From<ComboDTO>();
          var rtv = DbConnection.Select(sqlExpression).ToList();
          return rtv;
      }
        /// <summary>
        /// 根据价格 性别 类别搜索
        /// </summary>
        /// <param name="Price"></param>
        /// <param name="sex"></param>
        /// <param name="TypeId"></param>
        /// <returns></returns>
      public List<ComboDTO> GetComboS(Decimal Price, int sex, int TypeId)
      {
          var sqlExpression = DbConnection.From<ComboDTO>()
              .Where(m => (m.Price == Price));
              //.Where(c => {c.Price == Price && c.Sex == sex && c.TypeId == TypeId});
          var rtv = DbConnection.Select(sqlExpression).ToList();
          return rtv;
      }

        /// <summary>
        /// 根据类型获取体检套餐列表
        /// </summary>
        /// <param name="typeId"></param>
        /// <returns></returns>
      public List<ComboDTO> GetComboByType(int typeId)
      {
          using (var cnn=DbConnection)
          {
              var sql = cnn.From<ComboDTO>()
                  .Where(m => m.ComboTypeId == typeId);

              var rtv = cnn.Select(sql).ToList();
              return rtv;
          }
      }

      /// <summary>
      /// 根据地区ID获取体检套餐列表
      /// </summary>
      /// <param name="areaId"></param>
      /// <returns></returns>
      public List<combo2> GetComboByArea(int areaId)
      {
          using (var cnn = DbConnection)
          {
              var sql = cnn.From<combo2>()
                  .Where(m => m.aid == areaId);
              var rtv = cnn.Select(sql).ToList();
              //if (rtv.Count < 1)
              //{
              //    return null;
              //}
              //var hid = from s in rtv
              //          select s.Id;
              //var sql2 = cnn.From<ComboDTO>()
              //    .Where(m => Sql.In(m.HospitalId, hid));
              //var result = cnn.Select(sql2).ToList();
              return rtv;
          }
      }

      /// <summary>
      /// 根据地区ID获取体检套餐列表的数目
      /// </summary>
      /// <param name="areaId"></param>
      /// <returns></returns>
      public int GetComboByAreaCount(int areaId)
      {
          using (var cnn = DbConnection)
          {
              var sql = cnn.From<combo2>()
                  .Where(m => m.aid == areaId);
              var rtv = cnn.Select(sql).Count;
              //if (rtv.Count < 1)
              //{
              //    return 0;
              //}
              //var hid = from s in rtv
              //          select s.Id;
              //var sql2 = cnn.From<ComboDTO>()
              //    .Where(m => Sql.In(m.HospitalId, hid));
              //var result = cnn.Select(sql2).Count;
              return rtv;
          }
      }

      /// <summary>
      /// 按热门套餐价格排序全部信息
      /// </summary>
      /// <returns></returns>
      public List<ComboDTO> GetAllComboType2(int eid)
      {
          using (var cnn = DbConnection)
          {
              var rtv = cnn.Select<ComboDTO>()
                  .Where(m => m.ComboTypeId== eid).ToList()
                  .OrderByDescending(m => m.Price);
              return rtv.ToList();
          }
      }
      /// <summary>
      /// 按热门套餐价格排序前N条
      /// </summary>
      /// <returns></returns>
      public List<ComboDTO> GetAllComboType2N(int eid)
      {
          using (var cnn = DbConnection)
          {
              var rtv = cnn.Select<ComboDTO>()
                  .Where(m => m.ComboTypeId == eid).ToList()
                  .OrderByDescending(m => m.Price)
                  .Take(1);
              return rtv.ToList();
          }
      }

      /// <summary>
      /// 按热门套餐销量排序全部
      /// </summary>
      /// <returns></returns>
      public List<ComboDTO> GetAllComboType3(int eid)
      {
          using (var cnn = DbConnection)
          {
              var rtv = cnn.Select<ComboDTO>()
                  .Where(m => m.ComboTypeId == eid).ToList()
                  .OrderByDescending(m => m.Sonnum);
              return rtv.ToList();
          }
      }
      /// <summary>
      /// 按热门套餐销量排序全部
      /// </summary>
      /// <returns></returns>
      public List<ComboDTO> GetAllComboType3N(int eid)
      {
          using (var cnn = DbConnection)
          {
              var rtv = cnn.Select<ComboDTO>()
                  .Where(m => m.ComboTypeId == eid).ToList()
                  .OrderByDescending(m => m.Sonnum)
                  .Take(1);
              return rtv.ToList();
          }
      }


      /// <summary>
      /// 按热门套餐ID
      /// </summary>
      /// <returns></returns>
      public List<ComboDTO> GetAllComboType4(int eid)
      {
          using (var cnn = DbConnection)
          {
              var rtv = cnn.Select<ComboDTO>().Where(m => m.ComboTypeId == eid).Take(1).ToList();
              return rtv;
          }
      }
      /// <summary>
      /// 按热门套餐ID家加载全部
      /// </summary>
      /// <returns></returns>
      public List<ComboDTO> GetAllComboType5(int eid)
      {

          using (var cnn = DbConnection)
          {
              var rtv = cnn.Select<ComboDTO>().Where(m => m.ComboTypeId == eid).ToList();
              return rtv;
          }
      }

    }
}
